package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.SelectSupplierDao;
import com.clqteam.model.Supplier;
import com.clqteam.util.JDBCutil;

public class SelectSupplierDaoImpl implements SelectSupplierDao{
	/**
	 * 分页显示查询的信息
	 * @param keyword 关键字
	 * @param page 页面
	 * @param rows 页面大小
	 * @return List
	 */
	@Override
	public List<Supplier> selectSupplierD(String keyword,int page,int rows) {
		List <Supplier>li=new ArrayList();
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT s_supplier.supName,s_supplier.supContacts,s_supplier.supPhone,s_supplier.eId,s_supplier.supId,s_employee.eName");
		sql.append(" FROM s_supplier");
		sql.append(" INNER JOIN s_employee");
		sql.append(" ON s_employee.eId=s_supplier.eId");
		sql.append(" WHERE s_supplier.supName LIKE ?");
		sql.append(" OR s_supplier.supContacts LIKE ?");
		sql.append(" OR s_supplier.supPhone LIKE ?");
		sql.append(" LIMIT ?,?");
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());			
			ps.setString(1, "%"+keyword+"%");
			ps.setString(2, "%"+keyword+"%");
			ps.setString(3, "%"+keyword+"%");
			ps.setInt(4, (page-1)*rows);
			ps.setInt(5, rows);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				Supplier sp=new Supplier();
				sp.setSupId(rs.getInt("supId"));
				sp.setSupName(rs.getString("supName"));
				sp.setSupContacts(rs.getString("supContacts"));
				sp.setSupPhone(rs.getString("supPhone"));
				sp.seteId(rs.getInt("eId"));
				sp.seteName(rs.getString("eName"));
				li.add(sp);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return li;
	}
	/**
	 * 查询满足条件信息的总数
	 * @param keyword 关键字
	 * @return
	 */
	@Override
	public int countSupplier(String keyword) {
		int count=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT COUNT(s_supplier.supId) AS cou");
		sql.append(" FROM s_supplier");
		sql.append(" WHERE s_supplier.supName LIKE ?");
		sql.append(" OR s_supplier.supContacts LIKE ?");
		sql.append(" OR s_supplier.supPhone LIKE ?");
		
		Connection con=JDBCutil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());			
			ps.setString(1, "%"+keyword+"%");
			ps.setString(2, "%"+keyword+"%");
			ps.setString(3, "%"+keyword+"%");
			
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt("cou");
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
